
ALTER         proc sp_load_lineitems_for_packing_slip( 
  @custShiping_Id int, 
  @salesOrder_Id int, 
  @vendor_address_Id int  )
  As 
  	declare @results Table( lineItemId  int, CSLIT_Id int, qtyToShip int, SalesOrder_ID int, COOL_Line_Item_No int, 
  	Product_ID int, COOL_Qty int, Qty_Pulled int, Original_Qty_Pulled int, COOL_Unit_Cost float, COOL_Amount float, COOL_Requested_Date datetime, 
  	COOL_Booking_Date datetime, COOL_Sugg_Schedule_Date_With_Substitutables datetime,
  	COOL_Sugg_Schedule_Date_Without_Substitutables datetime, COOL_Schedule_Date datetime, 
  	COOL_Revised_Date datetime, COOL_Is_Cancel bit,	COOL_Status varchar(50), COOL_Taxable bit, 
  	COOL_Description varchar(200), COOL_PO_Line_Item_No int, Part_Number varchar(50), 
  	Mkt_Part_Number varchar(50), Customer_Part_Number varchar(50), Qty_Available int, Software_version varchar(50), 
  	ScheduleNumber varchar(50), Units varchar(50), Shipped_Part_Number varchar(1000), Parent_Part_Number varchar(50), Customer_Label_Number varchar(50), ODM_Part_Number varchar(50) )
  
  	declare @temp_lineItem		int
  	declare @CSLIT_ID	 	int
  	declare @COOL_Line_Item_No 	int
  	declare @COOL_PO_Line_Item_No 	int	
  	declare @Product_ID		int	
  
  	declare @COOL_Qty		int  -- Actual Quantity in Sales Order
  	declare @shipped_qty_sum 	int  -- Total Quantity Shipped upto now
  	declare @qty_to_be_shipped 	int  -- Remaining Quntity Shipped	
  	declare @qty_pulled		int  -- Quantity pulled for this PS
  	declare @qty_available		int  -- Inventory Available for the Product
  	declare @original_qty_pulled	int  -- This is for finding the original quantity pulled from CSLNT table
  
  	declare @COOL_Unit_Cost		float
  	declare @COOL_Amount		float
  
  	declare @COOL_Requested_Date	datetime
  	declare @COOL_Booking_Date	datetime
  	declare @COOL_Sugg_Schedule_Date_With_Substitutables    datetime
  	declare @COOL_Sugg_Schedule_Date_Without_Substitutables datetime
  	declare @COOL_Schedule_Date 	datetime
  	declare @COOL_Revised_Date 	datetime
  
  	declare @COOL_Is_Cancel		bit
  	declare @COOL_Status 		varchar(50)
  	declare @COOL_Taxable 		bit
  	declare @COOL_Description 	varchar(200)
  	declare @softwareVersion	varchar(50)
  	declare @scheduleNumber		varchar(50)
  	declare @units			varchar(50) 
  	
  	declare @mfg_part_number 	varchar(50)
  	declare @mkt_part_number 	varchar(50)
  	declare @cutomer_part_number 	varchar(50)
  	declare @temp_product_type	varchar(50)
  	declare @temp_process_step_id   int
  	declare @parent_product_id	int
 	declare @shipped_part_number 	varchar(1000)
	declare	@parent_part_number	varchar(50)
	declare @customer_label_number 	varchar(50) -- Added by Krishna Prasad on 23-11-04
	declare @odm_part_number 	varchar(50) 
  
  
  	declare lineItemCursor cursor for 
  	SELECT CSLIT_ID,  LineItem_ID, CSLIT_Qty_Shipped,  
  	CSLIT_Description, CSLIT_SoftwareVersion, CSLIT_CI_Schedule_Number,
  	CSLIT_Cust_Part_Number, CSLIT_Units, CSLIT_Shipped_Part_Number, CSLIT_Cust_Label_Number, CSLIT_ODM_Part_Number FROM 
  	Customer_Shipping_Line_Item_Table WHERE CustomerShipping_ID = @custShiping_Id
  
  	open lineItemCursor
  	fetch next from lineItemCursor into @CSLIT_ID, @temp_lineItem, @qty_pulled, @COOL_Description, @softwareVersion, @scheduleNumber, @cutomer_part_number, @units, @shipped_part_number, @customer_label_number, @odm_part_number 
  
  	while @@fetch_status = 0
  	begin
  		set @shipped_qty_sum = ( select  isnull( sum( CSLIT_Qty_Shipped ), 0 ) from Customer_Shipping_Line_Item_Table where LineItem_ID = @temp_lineItem AND CSLIT_Status != 'CANCELLED' )
  		set @qty_to_be_shipped  = ( select  ( avg( COOL_Qty ) - @shipped_qty_sum ) from Customer_Open_Order_LineItem where LineItem_ID = @temp_lineItem )
  
  		if( @qty_to_be_shipped + @qty_pulled > 0 )
  		begin				
  			SELECT  @COOL_Line_Item_No = COOL_Line_Item_No, @Product_ID = Product_ID, 
  			@COOL_Qty = COOL_Qty, @COOL_Unit_Cost = COOL_Unit_Cost, @COOL_Amount = COOL_Amount, 
  			@COOL_Requested_Date = convert (varchar, COOL_Requested_Date, 101), @COOL_Booking_Date = convert (varchar, COOL_Booking_Date, 101), 
  			@COOL_Sugg_Schedule_Date_With_Substitutables = convert (varchar, COOL_Sugg_Schedule_Date_With_Substitutables, 101) , 
  			@COOL_Sugg_Schedule_Date_Without_Substitutables = convert (varchar, COOL_Sugg_Schedule_Date_Without_Substitutables, 101), 
  			@COOL_Schedule_Date = convert (varchar, COOL_Schedule_Date, 101), @COOL_Revised_Date = convert (varchar, COOL_Revised_Date, 101), 
  			@COOL_Is_Cancel = COOL_Is_Cancel, @COOL_Status = COOL_Status, 
  			@COOL_Taxable = COOL_Taxable, 
  			@COOL_PO_Line_Item_No = COOL_PO_Line_Item_No FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @temp_lineItem
  
  			/*if( ( @COOL_Sugg_Schedule_Date_Without_Substitutables is null ) ) --Commented by Krishna Prasad 08 July 2004
  			begin
  				set @COOL_Sugg_Schedule_Date_Without_Substitutables = @COOL_Schedule_Date
  			end
  			else
  			begin
  				set @COOL_Schedule_Date = @COOL_Sugg_Schedule_Date_Without_Substitutables
  			end*/
  
  			--Added by Krishna Prasad 08 July 2004
  			if ( ( @COOL_Schedule_Date is null ) And ( @COOL_Sugg_Schedule_Date_Without_Substitutables is not null ) )
  			begin
  				set @COOL_Schedule_Date = @COOL_Sugg_Schedule_Date_Without_Substitutables
  			end
  
  			if ( ( @COOL_Sugg_Schedule_Date_Without_Substitutables is null ) And ( @COOL_Schedule_Date is not null ) ) 
  			begin
  				set @COOL_Sugg_Schedule_Date_Without_Substitutables = @COOL_Schedule_Date
  			end -- Till here
  			
  			-- Fetch MktPartNumber and MfgPartNumber	
  			SELECT @mfg_part_number = Part_Number, @mkt_part_number = P_Marketing_Part_Number from product where Product_Id = @Product_ID
  			
			-- Fetch Parent MktPartNumber
			SELECT @temp_product_type = ISNULL(p_type,'CHIP') FROM Product WHERE Product_Id = @Product_ID

			IF ( @temp_product_type = 'CHIP' )
			BEGIN
				SELECT @parent_part_number = Part_Number FROM Product WHERE Product_Id = @Product_ID
			END
			ELSE
			BEGIN
				SELECT @parent_product_id = P_Parent_Product_ID FROM Product WHERE Product_Id = @Product_ID
		
				IF ( @parent_product_id = -1)
				BEGIN	
					SELECT @parent_part_number = Part_Number FROM Product WHERE Product_Id = @Product_ID
				END
				ELSE		
				BEGIN
					SELECT @parent_part_number = Part_Number FROM product WHERE Product_id = ( SELECT P_Parent_Product_ID FROM product WHERE Product_Id = @Product_ID )
				END
			END	

			
  			-- Fetcb Quantity pulled from CSLNT
  			select @original_qty_pulled = sum( CSLNT_Qty_Pull ) from Customer_Shipping_Lot_Number_Table where CSLIT_ID = @CSLIT_ID
  						
  			-- This variable is set explicitly to 1 because it is in ISSUED Condition
  			set @qty_available = 1
  
  			insert into @results( lineItemId, CSLIT_Id, qtyToShip, SalesOrder_ID, COOL_Line_Item_No, 
  			Product_ID, COOL_Qty, Qty_Pulled, Original_Qty_Pulled, COOL_Unit_Cost, COOL_Amount, COOL_Requested_Date, 
  			COOL_Booking_Date, COOL_Sugg_Schedule_Date_With_Substitutables,
  			COOL_Sugg_Schedule_Date_Without_Substitutables, COOL_Schedule_Date, 
  			COOL_Revised_Date, COOL_Is_Cancel, COOL_Status, COOL_Taxable, COOL_Description, 
  			COOL_PO_Line_Item_No, Part_Number, Mkt_Part_Number, Customer_Part_Number, 
  			Qty_Available, Software_version, ScheduleNumber, Units, Shipped_Part_Number, Parent_Part_Number, Customer_Label_Number, ODM_Part_Number ) Values
  			( @temp_lineItem, @CSLIT_ID,@qty_to_be_shipped, @SalesOrder_ID, @COOL_Line_Item_No, 
  			@Product_ID, @COOL_Qty, @qty_pulled, @original_qty_pulled, @COOL_Unit_Cost, @COOL_Amount, @COOL_Requested_Date, 
  			@COOL_Booking_Date, @COOL_Sugg_Schedule_Date_With_Substitutables,
  			@COOL_Sugg_Schedule_Date_Without_Substitutables, @COOL_Schedule_Date, 
  			@COOL_Revised_Date, @COOL_Is_Cancel, @COOL_Status, @COOL_Taxable, @COOL_Description, 
  			@COOL_PO_Line_Item_No, @mfg_part_number, @mkt_part_number, @cutomer_part_number, 
  			@qty_available, @softwareVersion, @scheduleNumber, @units , @shipped_part_number , @parent_part_number, @customer_label_number, @odm_part_number)
  			
  			set @qty_available = 0
  			set @CSLIT_ID = -1	
  			set @temp_product_type = null 
  			set @cutomer_part_number = '' 
  			set @temp_lineItem = 0
  			set @shipped_qty_sum = 0
  			set @qty_to_be_shipped = 0
  			set @COOL_Line_Item_No = 0
  			set @Product_ID = 0	
  			set @COOL_Qty = 0
  			set @qty_pulled = 0
  			set @original_qty_pulled = 0
  			set @COOL_Unit_Cost = 0.0
  			set @COOL_Amount = 0.0 
  			set @COOL_Requested_Date = ''
  			set @COOL_Booking_Date = ''
  			set @COOL_Sugg_Schedule_Date_With_Substitutables = ''
  			set @COOL_Sugg_Schedule_Date_Without_Substitutables = ''
  			set @COOL_Schedule_Date = ''
  			set @COOL_Revised_Date = ''
  			set @COOL_Is_Cancel = 0
  			set @COOL_Status = ''
  			set @COOL_Taxable = 0
  			set @COOL_Description = ''
  			set @COOL_PO_Line_Item_No = 0
  			set @mfg_part_number = ''
  			set @mkt_part_number = ''
  			set @temp_process_step_id = 0
  			set @parent_product_id = 0
  			set @softwareVersion=''
  			set @scheduleNumber= ''
  			set @units = ''
 			set @shipped_part_number = ''
			set @parent_part_number = ''
			set @customer_label_number = ''
			set @odm_part_number = ''
  		end	
   
  		fetch next from lineItemCursor into @CSLIT_ID, @temp_lineItem, @qty_pulled, @COOL_Description, @softwareVersion, @scheduleNumber, @cutomer_part_number, @units, @shipped_part_number, @customer_label_number, @odm_part_number
  
  	end
  	
  	close lineItemCursor
  	deallocate lineItemCursor
  
  	select * from @results order by COOL_PO_Line_Item_No
